<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * @author Меркулов Илья aka Bit0reZ
 * @license GNU GPLv3
 */

class WebinarDB extends CI_Model {

        function  __construct()
        {
                parent::__construct();
        }

        function create($uid, $pid = NULL)
        {
		$query = $this->db->query('update planned set running = 1 where ID = '.$pid);
		return $pid;
        }

        function update($id, $data)
        {
                $this->db->where('id', $id);
                $this->db->update('planned', $data);
        }

        function end($id)
        {
		$this->db->query('call end('.$id.');');
                return true;
        }
	
	function join($data)
	{
		$this->db->insert('attendees', $data);
                return $this->db->insert_id();
	}
	
	function left($id, $UID)
	{
		$this->db->query('call `left`('.$id.','.$UID.');');
	}

	function is_running($id)
        {
                $this->db->where('id', $id)->where('running','1');
                $this->db->from('planned');
                $count = $this->db->count_all_results();
                return $count;
        }

        function own($uid, $id)
        {
                $this->db->where('UID', $uid);
                $this->db->where('id', $id);
                $this->db->from('planned');
                $query = $this->db->count_all_results();
                return $query;
        }

        function is_set($uid)
        {
                $this->db->where('UID', $uid);
                $this->db->where('running', '1');
		$this->db->from('planned');
                $count = $this->db->count_all_results();
                return $count;
        }

        function planned($uid, $all = false, $date = '')
        {
                $this->load->helper('date');
                $this->db->select('*');
                $this->db->from('planned');
                $this->db->where('running !=', '1');
                if(!$all) $this->db->where('UID', $uid);
                if($date != '') $this->db->where('date >=', $date);
                $this->db->order_by('date');
                $this->db->order_by('stime');
                $query = $this->db->get();
                $data = $query->result_array();
                for($i=0;;$i++)
                {
                        if(!isset($data[$i])) break;
                        $data[$i]['date'] = mdate('%d.%m.%Y',mysql_to_unix($data[$i]['date']));
                        $data[$i]['stime'] = substr($data[$i]['stime'], 0, 5);
                        $data[$i]['etime'] = substr($data[$i]['etime'], 0, 5);
                        $data[$i]['datetime'] = $data[$i]['date'].' '.$data[$i]['stime'].'-'.$data[$i]['etime'];
                        switch($data[$i]['status'])
			{
				case '0':
					$data[$i]['status'] = 'Открытый';
					break;
				case '1':
					$data[$i]['status'] = 'Закрытый';
					break;
			}
                }
                return $data;
        }

        function plannedData($id)
        {
                $this->db->select('subj, count, status');
                $this->db->from('planned');
                $this->db->where('id', $id);
		$this->db->where('running !=', '1');
                $query = $this->db->get();
                $data = $query->result_array();
                return $data[0];
        }

        function past($uid, $all = false, $date = '', $group = false, $from = 0)
        {
                $this->load->helper('date');
                $this->db->select('*')->from('past');
                if(!$all) $this->db->where('UID', $uid);
                if($date != '') $this->db->where('date', $date);
                $this->db->limit(10, $from);
                $this->db->order_by('date', 'desc');
                $this->db->order_by('stime');
		if($group) $this->db->group_by('UID, date, subj');
		$this->db->distinct();
                $query = $this->db->get();
                $data = $query->result_array();
                for($i=0;;$i++)
                {
                        if(!isset($data[$i])) break;
                        $data[$i]['date'] = mdate('%d.%m.%Y',mysql_to_unix($data[$i]['date']));
                        $data[$i]['stime'] = substr($data[$i]['stime'], 0, 5);
                        $data[$i]['etime'] = substr($data[$i]['etime'], 0, 5);
                        $data[$i]['datetime'] = $data[$i]['date'].' '.$data[$i]['stime'].'-'.$data[$i]['etime'];
                }
                return $data;
        }

        function plan($data, $max)
        {
		/*
		 * TODO: FIX IT!!!
		 */
                $this->db->where('uid', $data['uid']);
                $this->db->where('date',$data['date']);
                $this->db->where('stime >=', $data['stime']);
                $this->db->where('stime <=', $data['etime']);
                $this->db->from('planned');
                $count = $this->db->count_all_results();
                if($count)
                {
                        return FALSE;
                }
                else
                {
                        $this->db->where('date', $data['date']);
                        $this->db->where('stime >=', $data['stime']);
                        $this->db->where('stime <=', $data['etime']);
                        $this->db->from('planned');
                        $this->db->select_sum('count');
                        $query = $this->db->get();
                        $count = $query->result_array();
                        if(($count[0]['count'] + $data['count']) <= $max)
                        {
                                $this->db->insert('planned', $data);
                                return $this->db->insert_id();
                        }
                        else
                        {
                                return -1;
                        }
                }
        }

        function nowRunning()
        {
                $this->db->select('*');
                $this->db->from('planned');
		$this->db->where('running', '1');
                $query = $this->db->get();
                $run = $query->result_array();
		foreach($run as $key => $val)
                {
                        $run[$key]['stime'] = substr($run[$key]['stime'], 0, 5);
			$run[$key]['etime'] = substr($run[$key]['etime'], 0, 5);
                }
                return $run;
        }

        function maxParticipantCount($id)
        {
                $this->db->select('count');
                $this->db->from('planned');
                $this->db->where('running', '1');
                $query = $this->db->get();
                $data = $query->result_array();
                return $data[0]['count'];
        }

        function getUID($id)
        {
                $this->db->select('uid');
                $this->db->from('planned');
                $this->db->where('id', $id);
                $query = $this->db->get();
                $data = $query->result_array();
                return $data[0]['uid'];
        }
	
	function getPlUID($id)
        {
		return $this->getUID($id);
        }
	
	function calendarMonth($month, $year)
        {
                $this->load->helper('date');
                $what = 'subj, date, stime, etime, userdata';
                $where = 'WHERE date >= \''.$year."-".$month."-01'";
                $where .=' AND ';
                $where .= 'date <= \''.$year."-".$month."-".days_in_month($month, $year).'\'';
                $sql = 'SELECT '.$what.' FROM `webinar`.`planned` '.
                        $where
                        .' UNION SELECT '.$what.' FROM `webinar`.`past` '.
                        $where
                        .' ORDER BY date';
                $query = $this->db->query($sql);
                $data = $query->result_array();
                for($i=0;;$i++)
                {
                        if(!isset($data[$i])) break;
                        $data[$i]['date'] = mdate('%d',mysql_to_unix($data[$i]['date']));
                        $data[$i]['stime'] = substr($data[$i]['stime'], 0, 5);
                        $data[$i]['etime'] = substr($data[$i]['etime'], 0, 5);
                }
                $n=mdate('%N',mktime(0,0,0,$month,1,$year))-1;
                if($n > 0){
                        for($x=1; $x <=$n; $x++){
                                $cal[$x]['date'] = days_in_month($month-1, $year) -$n +$x;
                                $cal[$x]['subj'] = array(array('info' => 'none',
                                    'stime' => '', 'etime' => '', 'name' => ''));
                                }
                        }
                for($i=0,$x=1+$n;$x<=31+$n;$x++)
                {
                        if(!isset($data[$i])) $data[$i]['date'] = 0;
                        if($data[$i]['date'] != $x-$n)
                        {
                                $cal[$x]['date'] = $x-$n;
                                $cal[$x]['subj'] = array(array('info' => 'none',
                                    'stime' => '', 'etime' => '', 'name' => ''));
                        }
                        else
                        {
                                if(!isset($data[$i+1]) || $data[$i]['date'] != $data[$i+1]['date'])
                                {
                                        $cal[$x]['date'] = $data[$i]['date'];
                                        $cal[$x]['subj'] = array(array('info' => $data[$i]['subj'],
                                            'stime' => $data[$i]['stime'],
                                            'etime' => $data[$i]['etime'],
                                            'name' => $data[$i]['userdata']));
                                }
                                else
                                {
                                        $cal[$x]['date'] = $data[$i]['date'];
                                        $cal[$x]['subj'] = array(array('info' => $data[$i]['subj'],
                                            'stime' => $data[$i]['stime'],
                                            'etime' => $data[$i]['etime'],
                                            'name' => $data[$i]['userdata']));
                                        $j=$i;
                                        while(isset($data[$j+1]) && $data[$j]['date'] === $data[$j+1]['date'])
                                        {
                                                $cal[$x]['subj'][] = array('info' => $data[$j+1]['subj'],
                                                    'stime' => $data[$j+1]['stime'],
                                                    'etime' => $data[$j+1]['etime'],
                                                    'name' => $data[$j+1]['userdata']);
                                                $j++;
                                        }
                                        $i=$j;
                                }
                                $i++;
                        }
                }
                return $cal;
        }
	
	/*
	 * DB Cleaning
	 */

        function clean($UID = 0)
        {
                /*
                 * TODO: Доработать очистку базы
                 */

                $this->load->helper('date');
                /*if($UID == 0)
                        $this->db->query('CALL clean(\''.mdate('%Y-%m-%d',time()).'\',\''.
                                mdate('%H:%i:%s',time()).'\')');
                else $this->db->query('CALL cleanByUID(\''.mdate('%Y-%m-%d',time()).'\',\''.
                        mdate('%H:%i:%s',time()).'\', '.$UID.')');*/
        }

        function delete($id, $UID = 0)
        {
		$this->db->where('id', $id);
                $this->db->delete('planned');
        }

	function delPast($id, $UID = 0)
        {
		$this->db->where('id', $id);
                $this->db->delete('past');
		$this->db->where('wbid', $id);
		$this->db->delete('past_attendees');
        }
	
	/* 
	 * WARNINGS
	 */
	
	function warning()
	{
		$this->db->select('ws.text message, w.wsid id')->from('warnings ws');
		$this->db->join('warn w', 'w.wsid = ws.id');
		$this->db->where('w.status', '1');
		$query = $this->db->get();
		$data = $query->result_array();
		return $data;
	}
	
	function status_warn()
	{
		$this->db->select('ws.text message, w.wsid id, w.status status')->from('warnings ws');
		$this->db->join('warn w', 'w.wsid = ws.id');
		$query = $this->db->get();
		$data = $query->result_array();
		return $data;
	}
	
	function set_warn($status = -1, $id = -1)
	{
		if($status >= 0) $data['status'] = $status;
		if($id >= 0) $data['wsid'] = $id;
		$this->db->where('id', '1');
		$this->db->update('warn', $data);
	}
	
	function list_warn()
	{
		$this->db->select('id,text message')->from('warnings');
		$query = $this->db->get();
		$data = $query->result_array();
		return $data;
	}
	
	function add_warn($text)
	{
		$data['text'] = @$text;
		$this->db->insert('warnings', $data);
		return $this->db->insert_id();
	}
	
	function edit_warn($id, $text)
	{
		$data['text'] = $text;
		$this->db->where('id', $id);
		$this->db->update('warnings', $data);
	}
	
	/*
	 * Feedback
	 */
	
	function add_issue($data)
	{
		$this->db->insert('feedback', $data);
		return $this->db->insert_id();
	}
	
	function types()
	{
		$query = $this->db->get('fb_types');
		return $query->result_array();
	}
	
	function addtype($text)
	{
		$data['type'] = @$text;
		$this->db->insert('fb_types', $data);
	}
	
	function getTypeByID($id)
	{
		$this->db->where('id', $id);
		$this->db->select('type')->from('fb_types');
		$query = $this->db->get();
		$data = $query->result_array();
		return $data[0]['type'];
	}
	
	function issue_cnt($type)
	{
		$this->db->where('type', $type);
		$this->db->where('status', '1');
		$this->db->from('feedback');
		return $this->db->count_all_results();
	}
	
	function set_fbstat($id, $stat)
	{
		$this->db->where('id', $id);
		$this->db->set('status', $stat);
		$this->db->update('feedback');
	}
	
	function get_issues($type = '1', $status = '1')
	{
		$this->db->select('title, id, date')->from('feedback');
		$this->db->where('status', $status);
		$this->db->where('type',$type);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function vw_issue($id)
	{
		$this->db->select('date, type, title, text, entext')->from('feedback fb');
		$this->db->join('fb_status fbs', 'fb.status = fbs.id');
		$this->db->where('fb.id', $id);
		$query = $this->db->get();
		$data = $query->result_array();
		return $data[0];
	}
	
	function add_fbst($etext, $rtext)
	{
		$data['rutext'] = @$rtext;
		$data['entext'] = @$etext;
		$this->db->insert('fb_status', $data);
	}
	
	function fbstats()
	{
		$query = $this->db->get('fb_status');
		return $query->result_array();
	}
}